<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="${packageName}.dao.CmSysUserDao">

    <!--添加-->
    <insert id="add" parameterType="${packageName}.entity.CmSysUserEntity">
        insert into cm_sys_user
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="userId != null">
                user_id,
            </if>
            <if test="roleId != null">
                role_id,
            </if>
            <if test="username != null">
                username,
            </if>
            <if test="password != null">
                password,
            </if>
            <if test="createTime != null">
                create_time,
            </if>
            <if test="updateTime != null">
                update_time,
            </if>
            <if test="createUserId != null">
                create_user_id,
            </if>
            <if test="updateUserId != null">
                update_user_id,
            </if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="userId != null">
                <#noparse>#{</#noparse>userId},
            </if>
            <if test="roleId != null">
                <#noparse>#{</#noparse>roleId},
            </if>
            <if test="username != null">
                <#noparse>#{</#noparse>username},
            </if>
            <if test="password != null">
                <#noparse>#{</#noparse>password},
            </if>
            <if test="createTime != null">
                <#noparse>#{</#noparse>createTime},
            </if>
            <if test="updateTime != null">
                <#noparse>#{</#noparse>updateTime},
            </if>
            <if test="createUserId != null">
                <#noparse>#{</#noparse>createUserId},
            </if>
            <if test="updateUserId != null">
                <#noparse>#{</#noparse>updateUserId},
            </if>
        </trim>
    </insert>

    <!--删除-->
    <delete id="delete" parameterType="${packageName}.entity.CmSysUserEntity">
        delete from cm_sys_user
        <where>
            <if test="userId != null">
                and user_id=<#noparse>#{</#noparse>userId}
            </if>
            <if test="userId == null">
                and 1 = 0
            </if>
        </where>
    </delete>

    <!--更新-->
    <update id="update" parameterType="${packageName}.entity.CmSysUserEntity">
        update cm_sys_user
        <trim prefix="set" suffixOverrides=",">
            <if test="userId != null">
                user_id=<#noparse>#{</#noparse>userId},
            </if>
            <if test="roleId != null">
                role_id=<#noparse>#{</#noparse>roleId},
            </if>
            <if test="username != null">
                username=<#noparse>#{</#noparse>username},
            </if>
            <if test="password != null">
                password=<#noparse>#{</#noparse>password},
            </if>
            <if test="createTime != null">
                create_time=<#noparse>#{</#noparse>createTime},
            </if>
            <if test="updateTime != null">
                update_time=<#noparse>#{</#noparse>updateTime},
            </if>
            <if test="createUserId != null">
                create_user_id=<#noparse>#{</#noparse>createUserId},
            </if>
            <if test="updateUserId != null">
                update_user_id=<#noparse>#{</#noparse>updateUserId},
            </if>
        </trim>
        <where>
            <if test="userId != null">
                and user_id=<#noparse>#{</#noparse>userId}
            </if>
            <if test="userId == null">
                and 1 = 0
            </if>
        </where>
    </update>

    <!--固定条件查询-->
    <select id="select" parameterType="${packageName}.entity.CmSysUserEntity"
            resultType="${packageName}.entity.CmSysUserEntity">
        select
        user_id as "userId",
        role_id as "roleId",
        username as "username",
        password as "password",
        create_time as "createTime",
        update_time as "updateTime",
        create_user_id as "createUserId",
        update_user_id as "updateUserId"
       <#if dataBaseType == "oracle">
        from (select t2.*, ROWNUM rowno
        from (select t1.* FROM cm_sys_user t1
       <#else>
        from cm_sys_user
       </#if>
        <where>
            <if test="userId != null">
                and user_id=<#noparse>#{</#noparse>userId}
            </if>
            <if test="roleId != null">
                and role_id=<#noparse>#{</#noparse>roleId}
            </if>
            <if test="username != null">
                and username=<#noparse>#{</#noparse>username}
            </if>
            <if test="password != null">
                and password=<#noparse>#{</#noparse>password}
            </if>
            <if test="createTime != null">
                and create_time=<#noparse>#{</#noparse>createTime}
            </if>
            <if test="updateTime != null">
                and update_time=<#noparse>#{</#noparse>updateTime}
            </if>
            <if test="createUserId != null">
                and create_user_id=<#noparse>#{</#noparse>createUserId}
            </if>
            <if test="updateUserId != null">
                and update_user_id=<#noparse>#{</#noparse>updateUserId}
            </if>
        </where>
    <#if dataBaseType == "sqlserver">
        <if test="orderStr == '' or orderStr == null">
            order by user_id
        </if>
    </#if>
        <if test="orderStr != '' and orderStr != null">
            order by <#noparse>${</#noparse>orderStr}
        </if>
      <#if dataBaseType == "oracle">
        )t2)
        <if test="startIndex != null and endIndex != null">
        	<![CDATA[ WHERE ROWNUM <= <#noparse>#{</#noparse>endIndex<#noparse>}</#noparse>) t3
	 		WHERE t3.rowno > <#noparse>#{</#noparse>startIndex<#noparse>}</#noparse>]]>
        </if>
      <#elseif dataBaseType == "mysql">
        <if test="start != null and pageSize != null">
            limit <#noparse>#{</#noparse>start},<#noparse>#{</#noparse>pageSize}
        </if>
      <#elseif dataBaseType == "postgresql">
      	<if test="start != null and pageSize != null">
            limit <#noparse>#{</#noparse>pageSize<#noparse>}</#noparse> offset <#noparse>#{</#noparse>start<#noparse>}</#noparse>
        </if>
      <#elseif dataBaseType == "sqlserver">
         <if test="start != null and pageSize != null">
              offset <#noparse>#{</#noparse>start<#noparse>}</#noparse> rows fetch next <#noparse>#{</#noparse>pageSize<#noparse>}</#noparse> rows only
         </if>
      </#if>
    </select>

    <!--分页（模糊）查询 带roleName-->
    <select id="pageRole" parameterType="${packageName}.entity.CmSysUserEntity" resultType="${packageName}.entity.CmSysUserEntity">
    <#if dataBaseType == "oracle">
    	select * from (select a.*, ROWNUM rowno FROM (
    </#if>
        select
        csu.user_id,
        csu.role_id,
        csu.username,
        csu.password,
        csu.create_time,
        csu.update_time,
        csu.create_user_id,
        csu.update_user_id,
        csr.role_name
        FROM cm_sys_user csu
        LEFT JOIN cm_sys_role csr
        ON csu.role_id=csr.role_id
        <where>
            <if test="username != null and username != '' ">
                <#if dataBaseType == "mysql">
                 AND csu.username LIKE "%"<#noparse>#{</#noparse>username}"%"
                </#if>
                <#if dataBaseType == "oracle" || dataBaseType == "postgresql">
                AND csu.username LIKE '%' || <#noparse>#{</#noparse>username} || '%'
                </#if>
                <#if dataBaseType == "sqlserver">
                    AND csu.username LIKE '%' + <#noparse>#{</#noparse>username} + '%'
                </#if>
            </if>
        </where>
    <#if dataBaseType == "sqlserver">
        <if test="orderStr == '' or orderStr == null">
            order by user_id
        </if>
    </#if>
        <if test="orderStr != '' and orderStr != null">
            order by <#noparse>${</#noparse>orderStr}
        </if>
    <#if dataBaseType == "oracle">
    	) a
     	<if test="startIndex != null and endIndex != null">
    		<![CDATA[ WHERE ROWNUM<=<#noparse>#{</#noparse>endIndex<#noparse>}</#noparse>) WHERE rowno><#noparse>#{</#noparse>startIndex<#noparse>}</#noparse>]]>
    	</if>
    <#elseif dataBaseType == "mysql">
        <if test="start != null and pageSize != null">
            limit <#noparse>#{</#noparse>start},<#noparse>#{</#noparse>pageSize}
        </if>
	<#elseif dataBaseType == "postgresql">
	  	<if test="start != null and pageSize != null">
	        limit <#noparse>#{</#noparse>pageSize<#noparse>}</#noparse> offset <#noparse>#{</#noparse>start<#noparse>}</#noparse>
	    </if>
    <#elseif dataBaseType == "sqlserver">
        <if test="start != null and pageSize != null">
            offset <#noparse>#{</#noparse>start<#noparse>}</#noparse> rows fetch next <#noparse>#{</#noparse>pageSize<#noparse>}</#noparse> rows only
        </if>
	</#if>
    </select>

    <!--分页（模糊）查询 带roleName-->
    <select id="pageRoleCount" parameterType="${packageName}.entity.CmSysUserEntity" resultType="java.lang.Long">
        SELECT
        COUNT(1)
        FROM cm_sys_user csu
        LEFT JOIN cm_sys_role csr
        ON csu.role_id=csr.role_id
        <where>
            <if test="username != null and username != '' ">
                <#if dataBaseType == "mysql">
                    AND csu.username LIKE "%"<#noparse>#{</#noparse>username}"%"
                </#if>
                <#if dataBaseType == "oracle" || dataBaseType == "postgresql">
                    AND csu.username LIKE '%' || <#noparse>#{</#noparse>username} || '%'
                </#if>
                <#if dataBaseType == "sqlserver">
                    AND csu.username LIKE '%' + <#noparse>#{</#noparse>username} + '%'
                </#if>
            </if>
        </where>
    </select>
    <!--根据用户名获取-->
    <select id="getByUserName" resultType="${packageName}.entity.CmSysUserEntity">
        SELECT * FROM cm_sys_user WHERE username=<#noparse>#{</#noparse>username}
    </select>

    <select id="getById" resultType="${packageName}.entity.CmSysUserEntity">
         SELECT * FROM cm_sys_user WHERE user_id=<#noparse>#{</#noparse>userId}
    </select>
</mapper>
